//
//  DataBase.m
//  Lesson_17_Sqlite
//
//  Created by dllo on 15/8/25.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBase.h"


@implementation DataBase

+(DataBase *)shareDataBase
{
    static DataBase *dbHandle = nil;
    if (dbHandle == nil) {
        dbHandle = [[DataBase alloc]init];
    }
    
    return dbHandle;
 
}

static sqlite3 *db;// 创建一个数据库对象

#pragma mark - 打开数据库
-(void)openDB
{
    //获取沙盒路径
    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    //拼接sqlite文件路径
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"qyer.sqlite"];
//    NSLog(@"路径 = %@", sqlitePath);
    
    if (db != nil) {
//        NSLog(@"数据库已经打开");
        return;
    }
    
    // 打开数据库的方法
    // 把文件对象和数据库对象关联到一起
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    
    if (result == SQLITE_OK) {
//        NSLog(@"数据打开成功");
    }else{
//        NSLog(@"数据库打开失败");
    }
    
                              
}

#pragma mark - 创建数据库表
-(void)createTable
{
    // 生成创建表的sql语句,大写表示以后不用改,固定的,小写的是表名,以后可以改
    
    NSString *creatSql = @"CREATE TABLE IF NOT EXISTS qyer(number INTEGER PRIMARY KEY AUTOINCREMENT, cnname TEXT,enname TEXT,pic TEXT,cid TEXT)";
    // 执行sql语句
    
    int result = sqlite3_exec(db, creatSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
//        NSLog(@"创建数据库表成功");
    }else{
//        NSLog(@"创建数据库表失败");
    }
  
}

#pragma mark - 插入信息(增)
-(void)insertAct:(MyCollectModel *)model
{
    // 创建插入信息的sql语句
    NSString *inserSql = [NSString stringWithFormat:@"INSERT INTO qyer(cnname,enname,pic,cid) VALUES('%@','%@','%@','%@')", model.cnname,model.enname,model.pic,model.cid];
    
    // 执行sql语句
    int result = sqlite3_exec(db, inserSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"插入信息成功");
    }else{
//        NSLog(@"插入信息失败");
    }

}



#pragma mark - 删除一条学生信息

-(void)deleteAct:(NSInteger)number
{
    // 创建删除信息的sql语句
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM qyer WHERE number = '%ld'", number];
    // 执行sql语句
    int result = sqlite3_exec(db , deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除一条学生信息成功");
    }else{
//        NSLog(@"删除失败");
    }
 
    
}

#pragma mark - 查找所有信息
-(NSMutableArray *)selectALL
{
    NSString *selectSQl = @"SELECT * FROM qyer";
    
    // 创建数据库跟随指针, 用来循环遍历数据库表的每行数据
    sqlite3_stmt *stmt = nil;
    
    // 参数1:数据库对象 , 参数2:查询语句, 参数3:查询语句字数限制(-1为不限制), 参数4:跟随指针对象 , 参数5:nil
    int result = sqlite3_prepare(db, selectSQl.UTF8String, -1, &stmt, nil);
    
    // 创建存放信息的大数组
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
//        NSLog(@"查询准备工作完成");
    
    // 开始循环遍历查询数据库每一条数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 当跟随指针指向的行的数据符合你的查询条件,逐一取出每列数据
        
        int number = sqlite3_column_int(stmt, 0);
        const unsigned char *cnname = sqlite3_column_text(stmt, 1);
        const unsigned char *enname = sqlite3_column_text(stmt, 2);
        const unsigned char *pic = sqlite3_column_text(stmt, 3);
        const unsigned char *cid = sqlite3_column_text(stmt, 4);
       // const unsigned char *image = sqlite3_column_text(stmt, 2);
        
        
        
        NSString *cnnameStr = [NSString stringWithUTF8String:(const char *)cnname];
        NSString *ennameStr = [NSString stringWithUTF8String:(const char *)enname];
        NSString *picStr = [NSString stringWithUTF8String:(const char *)pic];
        NSString *cidStr = [NSString stringWithUTF8String:(const char *)cid];

        MyCollectModel *model = [[MyCollectModel alloc]init];
        model.cnname = cnnameStr;
        model.enname = ennameStr;
        model.pic = picStr;
        model.cid = cidStr;
//        model.image = imageString;
        model.number = number;
        
        [array addObject:model];
        
    }
        
    }else{
//        NSLog(@"查询失败");
    }
    
    
    return array;
}


#pragma mark - 关闭数据库
-(void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK ) {
//        NSLog(@"关闭数据库成功");
        db = nil;
    }else{
//        NSLog(@"关闭数据库失败");
    }
  
}

#pragma mark - 删除数据库表
-(void)dropTable
{
    NSString *dropSql = @"DROP TABLE qyer";
    int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除表成功");
        
    }else{
//        NSLog(@"删除表失败");
    }
 
}

-(void)deleteWithCnname:(NSString *)cnname
{
    NSString *deleSql = [NSString stringWithFormat:@"DELETE FROM qyer WHERE cnname = '%@'", cnname];
    int result = sqlite3_exec(db, deleSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除成功");
    }else{
//        NSLog(@"删除失败");
    }
}











@end
